package com.aizhixin.test.question.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aizhixin.test.question.dto.anserEverydayDTO;
import com.aizhixin.test.question.dto.judgeQuestionNeedDTO;


@Component
public class answerEverydayDAO {
/*
 * 看一个用户的每日答题信息
 * 	ID
	USER_ID
	ANSWER_RIGHT_STEM_NUMBERS
	ANSWER_NOT_RIGHT_STEM_NUMBERS
	TOTAL_ANSWR_NUMBERS
	ALL_POINTS
	TODAY_POINTS
	ANSWER_DATE
	SELECT q.USER_ID,q.ANSWER_RIGHT_STEM_NUMBERS,q.ANSWER_NOT_RIGHT_STEM_NUMBERS,q.TOTAL_ANSWR_NUMBERS,q.ALL_POINTS,q.TODAY_POINTS,q.ANSWER_DATE FROM answer_everday as q WHERE q.USER_ID=4
 * /	
 */
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public List<anserEverydayDTO> getOneAnserImformation(int userId){
		final String sql="SELECT q.USER_ID,q.ANSWER_RIGHT_STEM_NUMBERS,q.ANSWER_NOT_RIGHT_STEM_NUMBERS,q.TOTAL_ANSWR_NUMBERS,q.ALL_POINTS,q.TODAY_POINTS,q.ANSWER_DATE FROM answer_everday as q WHERE q.USER_ID=? ORDER BY q.ANSWER_DATE DESC";
		List<anserEverydayDTO> list=jdbcTemplate.query(sql, 
				new Object []{userId}, 
				new int[] {Types.INTEGER},
				new RowMapper<anserEverydayDTO>() {
					public anserEverydayDTO mapRow(ResultSet rs,
							int rowNum)throws SQLException{
						anserEverydayDTO test=new anserEverydayDTO();
						test.setUserId(rs.getLong("USER_ID"));
						test.setRightTotal(rs.getLong("ANSWER_RIGHT_STEM_NUMBERS"));
						test.setErrolTotal(rs.getLong("ANSWER_NOT_RIGHT_STEM_NUMBERS"));
						test.setTotalAnswewr(rs.getLong("TOTAL_ANSWR_NUMBERS"));
						test.setAllPoint(rs.getLong("ALL_POINTS"));
						test.setEverydayAllPoint(rs.getLong("TODAY_POINTS"));
						test.setAnserDate(rs.getTimestamp("ANSWER_DATE"));
						return test;
						
					}
				});
		if (null != list && list.size() > 0) {
			return list;
		}else {
			return null;
		}
	
	}
	/*
	 * 查表
	 * SELECT 
	 * q.USER_ID,
	 * q.ANSWER_RIGHT_STEM_NUMBERS,
	 * q.ANSWER_NOT_RIGHT_STEM_NUMBERS,
	 * q.TOTAL_ANSWR_NUMBERS,
	 * q.ALL_POINTS,
	 * q.TODAY_POINTS,
	 * q.ANSWER_DATE 
	 * FROM answer_everday as q WHERE q.USER_ID=? and DATE(q.ANSWER_DATE) = CURDATE()
	 * 
	 */
	public List<anserEverydayDTO> getuserEverdayHaveOrNotByUserIdAndDate(judgeQuestionNeedDTO dto){
		final String sql="SELECT q.USER_ID,q.ANSWER_RIGHT_STEM_NUMBERS,q.ANSWER_NOT_RIGHT_STEM_NUMBERS,q.TOTAL_ANSWR_NUMBERS,q.ALL_POINTS,q.TODAY_POINTS,q.ANSWER_DATE FROM answer_everday as q WHERE q.USER_ID=? and DATE(q.ANSWER_DATE) = CURDATE()";
		List<anserEverydayDTO> list =jdbcTemplate.query(sql, 
				new Object []{dto.getUserid()}, 
				new int[] {Types.INTEGER},
				new RowMapper<anserEverydayDTO>() {
					public anserEverydayDTO mapRow(ResultSet rs,
							int rowNum)throws SQLException{
						anserEverydayDTO test=new anserEverydayDTO();
						test.setUserId(rs.getLong("USER_ID"));
						test.setRightTotal(rs.getLong("ANSWER_RIGHT_STEM_NUMBERS"));
						test.setErrolTotal(rs.getLong("ANSWER_NOT_RIGHT_STEM_NUMBERS"));
						test.setTotalAnswewr(rs.getLong("TOTAL_ANSWR_NUMBERS"));
						test.setAllPoint(rs.getLong("ALL_POINTS"));
						test.setEverydayAllPoint(rs.getLong("TODAY_POINTS"));
						test.setAnserDate(rs.getTimestamp("ANSWER_DATE"));
						return test;
						
					}
				});
		if (null != list && list.size() > 0) {
			return list;
		}else {
			return null;
		}}
	/*
	 * INSERT into answer_everday 
	 * (USER_ID,
	 * ANSWER_RIGHT_STEM_NUMBERS,
	 * ANSWER_NOT_RIGHT_STEM_NUMBERS,
	 * TOTAL_ANSWR_NUMBERS,
	 * ALL_POINTS,
	 * TODAY_POINTS,
	 * ANSWER_DATE) VALUES(?,?,?,?,?,?,NOW())

	 */
	@Autowired
	judgeDAO judgeDAO;
	public int addanserEveryday(judgeQuestionNeedDTO dto) {
		final String sql="INSERT into answer_everday (USER_ID,ANSWER_RIGHT_STEM_NUMBERS,ANSWER_NOT_RIGHT_STEM_NUMBERS,TOTAL_ANSWR_NUMBERS,ALL_POINTS,TODAY_POINTS,ANSWER_DATE) VALUES(?,?,?,?,?,?,NOW())";
		
		Long m=judgeDAO.cheackcanDebuffOrNot(dto.getUserid());
		int a=m.intValue();
		if(dto.getRightOrNot()==1) {
			jdbcTemplate.update(ConcatExpression ->{
				PreparedStatement ps =ConcatExpression.prepareStatement(sql);
				ps.setLong(1, dto.getUserid());
				ps.setInt(2, 1);
				ps.setInt(3, 0);
				ps.setInt(4, 1);
				ps.setInt(5, a);
				ps.setInt(6, 3);
				
				
				return ps;
			});
		}
		else {
			jdbcTemplate.update(ConcatExpression ->{
				PreparedStatement ps =ConcatExpression.prepareStatement(sql);
				ps.setLong(1, dto.getUserid());
				ps.setInt(2, 0);
				ps.setInt(3, 1);
				ps.setLong(4, 1);
				ps.setInt(5,a );
				ps.setInt(6, 0);
				
				return ps;
			});
		}
		return 200;
	}
	/*
	 * UPDATE answer_everday SET 
	 * ANSWER_RIGHT_STEM_NUMBERS=ANSWER_RIGHT_STEM_NUMBERS+?,
	 * ANSWER_NOT_RIGHT_STEM_NUMBERS=ANSWER_NOT_RIGHT_STEM_NUMBERS+?,
	 * TOTAL_ANSWR_NUMBERS=TOTAL_ANSWR_NUMBERS+1,
	 * ALL_POINTS=ALL_POINTS+?,
	 * TODAY_POINTS=TODAY_POINTS+?,
	 * ANSWER_DATE=NOW() WHERE
	 *  USER_ID=? AND DATE(ANSWER_DATE) = CURDATE()

	 */
	public int changeAnserEveryday(judgeQuestionNeedDTO dto) {
	final String sql="UPDATE answer_everday SET ANSWER_RIGHT_STEM_NUMBERS=ANSWER_RIGHT_STEM_NUMBERS+?,ANSWER_NOT_RIGHT_STEM_NUMBERS=ANSWER_NOT_RIGHT_STEM_NUMBERS+?,TOTAL_ANSWR_NUMBERS=TOTAL_ANSWR_NUMBERS+1,ALL_POINTS=ALL_POINTS+?,TODAY_POINTS=TODAY_POINTS+?,ANSWER_DATE=NOW() WHERE USER_ID=? AND DATE(ANSWER_DATE) = CURDATE()";
		if(dto.getRightOrNot()==1) {
			jdbcTemplate.update(ConcatExpression ->{
				PreparedStatement ps =ConcatExpression.prepareStatement(sql);
				ps.setInt(1,1);
				ps.setInt(2,0);
				ps.setInt(3, dto.getPoint());
				ps.setInt(4, dto.getPoint());
				ps.setLong(5, dto.getUserid());
				
				return ps;
			});
		}
		else {
			jdbcTemplate.update(ConcatExpression ->{
				PreparedStatement ps =ConcatExpression.prepareStatement(sql);
				ps.setInt(1,0);
				ps.setInt(2,1);
				ps.setInt(3, dto.getPoint());
				ps.setInt(4, dto.getPoint());
				ps.setLong(5, dto.getUserid());
				
				return ps;
			});
		}
		return 200;
	}
}
